﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using Fuyao.Business.Entities.Models;


namespace Fuyao.Data.SQLServer.DAO
{
    public class DAOConductor
    {
        string strCnx = clsConexionDB.getConexionSQL();

        public bool InsertConductor(Conductor BE)
        {
            bool _result = false;
            StringBuilder strCommand = new StringBuilder();
            SqlConnection cnx = new SqlConnection(strCnx);

            try
            {
                strCommand.Append("INSERT INTO dbo.Conductor (NombreConductor, ApellidoConductor,LicenciaConducir,FechaCreacion,CreadoPor, Estado) ");
                strCommand.Append("VALUES (@NombreConductor,@ApellidoConductor,@LicenciaConducir,getDate(),@CreadoPor,1)");

                SqlCommand sqlCommand = new SqlCommand(strCommand.ToString(), cnx);
                sqlCommand.Parameters.Add("@NombreConductor", SqlDbType.VarChar, 30).Value = BE.NombreConductor;
                sqlCommand.Parameters.Add("@ApellidoConductor", SqlDbType.VarChar, 100).Value = BE.ApellidoConductor;
                sqlCommand.Parameters.Add("@LicenciaConducir", SqlDbType.VarChar, 10).Value = BE.LicenciaConducir;
                sqlCommand.Parameters.Add("@CreadoPor", SqlDbType.Int).Value = BE.CreadoPor;
                cnx.Open();
                _result = Convert.ToBoolean(sqlCommand.ExecuteNonQuery());

            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                cnx.Close();
            }
            return _result;
        }

        public bool UpdateConductor(Conductor BE)
        {
            bool _result = false;
            StringBuilder strCommand = new StringBuilder();
            SqlConnection cnx = new SqlConnection(strCnx);

            try
            {
                strCommand.Append("UPDATE dbo.Conductor set NombreConductor=@NombreConductor, ApellidoConductor=@ApellidoConductor,LicenciaConducir=@LicenciaConducir, Estado=@Estado,FechaModificacion=getDate(),ModificadoPor=@ModificadoPor  ");
                strCommand.Append("WHERE ConductorID = @ConductorID ");

                SqlCommand sqlCommand = new SqlCommand(strCommand.ToString(), cnx);
                sqlCommand.Parameters.Add("@ConductorID", SqlDbType.Int).Value = BE.ConductorID;
                sqlCommand.Parameters.Add("@NombreConductor", SqlDbType.VarChar, 30).Value = BE.NombreConductor;
                sqlCommand.Parameters.Add("@ApellidoConductor", SqlDbType.VarChar, 100).Value = BE.ApellidoConductor;
                sqlCommand.Parameters.Add("@LicenciaConducir", SqlDbType.VarChar, 10).Value = BE.LicenciaConducir;
                sqlCommand.Parameters.Add("@Estado", SqlDbType.Bit).Value = BE.Estado;
                sqlCommand.Parameters.Add("@ModificadoPor", SqlDbType.Int).Value = BE.CreadoPor;
                cnx.Open();
                _result = Convert.ToBoolean(sqlCommand.ExecuteNonQuery());

            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                cnx.Close();
            }
            return _result;
        }

        public bool DeleteConductor(int ConductorID, int idUserLogueado)
        {
            bool _result = false;
            StringBuilder strCommand = new StringBuilder();
            SqlConnection cnx = new SqlConnection(strCnx);

            try
            {
                strCommand.Append("UPDATE dbo.Conductor set Estado=@Estado,FechaModificacion=getDate(),ModificadoPor=@ModificadoPor  ");
                strCommand.Append("WHERE ConductorID = @ConductorID ");

                SqlCommand sqlCommand = new SqlCommand(strCommand.ToString(), cnx);
                sqlCommand.Parameters.Add("@ConductorID", SqlDbType.Int).Value = ConductorID;
                sqlCommand.Parameters.Add("@Estado", SqlDbType.Bit).Value = false;
                sqlCommand.Parameters.Add("@ModificadoPor", SqlDbType.Int).Value = idUserLogueado;
                cnx.Open();
                _result = Convert.ToBoolean(sqlCommand.ExecuteNonQuery());

            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                cnx.Close();
            }
            return _result;
        }

        public List<Conductor> getConductorByFilter(Conductor BE)
        {
            Conductor oConductor = null;
            StringBuilder strCommand = new StringBuilder();
            SqlConnection cnx = new SqlConnection(strCnx);
            List<Conductor> listConductores = new List<Conductor>();

            try
            {
                strCommand.Append("FYAO_getConductorByFiler");
                SqlCommand sqlCommand = new SqlCommand(strCommand.ToString(), cnx);
                sqlCommand.CommandType = CommandType.StoredProcedure;

                sqlCommand.Parameters.Add("@NombreConductor", SqlDbType.VarChar, 30).Value = BE.NombreConductor;
                sqlCommand.Parameters.Add("@ApellidoConductor", SqlDbType.VarChar, 100).Value = BE.ApellidoConductor;
                sqlCommand.Parameters.Add("@LicenciaConducir", SqlDbType.VarChar, 10).Value = BE.LicenciaConducir;
                sqlCommand.Parameters.Add("@Estado", SqlDbType.Bit).Value = BE.Estado;

                cnx.Open();
                SqlDataReader dr = sqlCommand.ExecuteReader();

                while (dr.Read())
                {
                    oConductor = new Conductor();
                    if (!Convert.IsDBNull(dr["ConductorID"])) oConductor.ConductorID = Convert.ToInt32(dr["ConductorID"]);
                    if (!Convert.IsDBNull(dr["NombreConductor"])) oConductor.NombreConductor = Convert.ToString(dr["NombreConductor"]);
                    if (!Convert.IsDBNull(dr["ApellidoConductor"])) oConductor.ApellidoConductor = Convert.ToString(dr["ApellidoConductor"]);
                    if (!Convert.IsDBNull(dr["LicenciaConducir"])) oConductor.LicenciaConducir = Convert.ToString(dr["LicenciaConducir"]);
                    if (!Convert.IsDBNull(dr["Estado"])) oConductor.Estado = Convert.ToBoolean(dr["Estado"]);                    
                    listConductores.Add(oConductor);
                }
                dr.Close();
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                cnx.Close();
            }
            return listConductores;
        }

        public Conductor getConductorById(int ConductorID)
        {
            Conductor oConductor = null;
            StringBuilder strCommand = new StringBuilder();
            SqlConnection cnx = new SqlConnection(strCnx);

            try
            {
                strCommand.Append("SELECT ConductorID,NombreConductor,ApellidoConductor,LicenciaConducir,Estado FROM Conductor  WHERE ConductorID=@ConductorID order by 1 ");
                SqlCommand sqlCommand = new SqlCommand(strCommand.ToString(), cnx);
                sqlCommand.CommandType = CommandType.Text;

                sqlCommand.Parameters.Add("@ConductorID", SqlDbType.Int).Value = ConductorID;

                cnx.Open();
                SqlDataReader dr = sqlCommand.ExecuteReader();

                if (dr.Read())
                {
                    oConductor = new Conductor();
                    if (!Convert.IsDBNull(dr["ConductorID"])) oConductor.ConductorID = Convert.ToInt32(dr["ConductorID"]);
                    if (!Convert.IsDBNull(dr["NombreConductor"])) oConductor.NombreConductor = Convert.ToString(dr["NombreConductor"]);
                    if (!Convert.IsDBNull(dr["ApellidoConductor"])) oConductor.ApellidoConductor = Convert.ToString(dr["ApellidoConductor"]);
                    if (!Convert.IsDBNull(dr["LicenciaConducir"])) oConductor.LicenciaConducir = Convert.ToString(dr["LicenciaConducir"]);
                    if (!Convert.IsDBNull(dr["Estado"])) oConductor.Estado = Convert.ToBoolean(dr["Estado"]);  
                }

                dr.Close();
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                cnx.Close();
            }
            return oConductor;
        }


        public string getLicenciaConducirByConductorID(int ConductorID)
        {
            StringBuilder strCommand = new StringBuilder();
            SqlConnection cnx = new SqlConnection(strCnx);
            string result = string.Empty;
            try
            {
                strCommand.Append("select LicenciaConducir from conductor where ConductorID=@ConductorID ");
                SqlCommand sqlCommand = new SqlCommand(strCommand.ToString(), cnx);
                sqlCommand.CommandType = CommandType.Text;
                //--------------------PARAMETROS CLIENTE
                sqlCommand.Parameters.Add("@ConductorID", SqlDbType.Int).Value = ConductorID;
                cnx.Open();
                result = Convert.ToString(sqlCommand.ExecuteScalar());
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                cnx.Close();
            }
            return result;
        }
    }
}
